<!DOCTYPE html>
<html lang="zh-CN">
<head>
  <meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=2">
<meta name="theme-color" content="#222">
<meta name="generator" content="Hexo 7.2.0">
  <link rel="apple-touch-icon" sizes="180x180" href="/images/ME.ico">
  <link rel="icon" type="image/png" sizes="32x32" href="/images/ME.ico">
  <link rel="icon" type="image/png" sizes="16x16" href="/images/ME.ico">
  <link rel="mask-icon" href="/images/ME.ico" color="#222">

<link rel="stylesheet" href="/css/main.css">


<link rel="stylesheet" href="/lib/font-awesome/css/all.min.css">

<script id="hexo-configurations">
    var NexT = window.NexT || {};
    var CONFIG = {"hostname":"www.zhoubencheng.com","root":"/","scheme":"Pisces","version":"7.8.0","exturl":false,"sidebar":{"position":"left","display":"post","padding":18,"offset":12,"onmobile":false},"copycode":{"enable":false,"show_result":true,"style":"mac"},"back2top":{"enable":true,"sidebar":false,"scrollpercent":true},"bookmark":{"enable":false,"color":"#222","save":"auto"},"fancybox":false,"mediumzoom":false,"lazyload":false,"pangu":false,"comments":{"style":"tabs","active":null,"storage":true,"lazyload":false,"nav":null},"algolia":{"hits":{"per_page":10},"labels":{"input_placeholder":"Search for Posts","hits_empty":"We didn't find any results for the search: ${query}","hits_stats":"${hits} results found in ${time} ms"}},"localsearch":{"enable":false,"trigger":"auto","top_n_per_article":1,"unescape":false,"preload":false},"motion":{"enable":true,"async":false,"transition":{"post_block":"fadeIn","post_header":"slideDownIn","post_body":"slideDownIn","coll_header":"slideLeftIn","sidebar":"slideUpIn"}}};
  </script>

  <meta name="description" content="SQL语句 参考教程：廖雪峰老师的SQL教程 数据类型         名称 类型 备注     INT 整型 4bit   BIGINT 长整型 8bit   REAL 单精度浮点型 4bit   DOUBLE 双精度浮点型 8bit   DECIMAL(M, N) 高精度小数 M表示总共位数，N表示小数点位数   CHAR(N) 定长字符串 N个字符的字符串，插入不满足N个的字符串用空格填充">
<meta property="og:type" content="article">
<meta property="og:title" content="SQL">
<meta property="og:url" content="http://www.zhoubencheng.com/2024/07/03/SQL/index.html">
<meta property="og:site_name" content="Benson&#39;s blog">
<meta property="og:description" content="SQL语句 参考教程：廖雪峰老师的SQL教程 数据类型         名称 类型 备注     INT 整型 4bit   BIGINT 长整型 8bit   REAL 单精度浮点型 4bit   DOUBLE 双精度浮点型 8bit   DECIMAL(M, N) 高精度小数 M表示总共位数，N表示小数点位数   CHAR(N) 定长字符串 N个字符的字符串，插入不满足N个的字符串用空格填充">
<meta property="og:locale" content="zh_CN">
<meta property="article:published_time" content="2024-07-02T19:28:58.000Z">
<meta property="article:modified_time" content="2024-10-08T12:59:39.780Z">
<meta property="article:author" content="Benson">
<meta property="article:tag" content="数据库">
<meta property="article:tag" content="SQL">
<meta name="twitter:card" content="summary">

<link rel="canonical" href="http://www.zhoubencheng.com/2024/07/03/SQL/">


<script id="page-configurations">
  // https://hexo.io/docs/variables.html
  CONFIG.page = {
    sidebar: "",
    isHome : false,
    isPost : true,
    lang   : 'zh-CN'
  };
</script>

  <title>SQL | Benson's blog</title>
  






  <noscript>
  <style>
  .use-motion .brand,
  .use-motion .menu-item,
  .sidebar-inner,
  .use-motion .post-block,
  .use-motion .pagination,
  .use-motion .comments,
  .use-motion .post-header,
  .use-motion .post-body,
  .use-motion .collection-header { opacity: initial; }

  .use-motion .site-title,
  .use-motion .site-subtitle {
    opacity: initial;
    top: initial;
  }

  .use-motion .logo-line-before i { left: initial; }
  .use-motion .logo-line-after i { right: initial; }
  </style>
</noscript>


<style>.github-emoji { position: relative; display: inline-block; width: 1.2em; min-height: 1.2em; overflow: hidden; vertical-align: top; color: transparent; }  .github-emoji > span { position: relative; z-index: 10; }  .github-emoji img, .github-emoji .fancybox { margin: 0 !important; padding: 0 !important; border: none !important; outline: none !important; text-decoration: none !important; user-select: none !important; cursor: auto !important; }  .github-emoji img { height: 1.2em !important; width: 1.2em !important; position: absolute !important; left: 50% !important; top: 50% !important; transform: translate(-50%, -50%) !important; user-select: none !important; cursor: auto !important; } .github-emoji-fallback { color: inherit; } .github-emoji-fallback img { opacity: 0 !important; }</style>
</head>

<body itemscope itemtype="http://schema.org/WebPage">
  <div class="container use-motion">
    <div class="headband"></div>

    <header class="header" itemscope itemtype="http://schema.org/WPHeader">
      <div class="header-inner"><div class="site-brand-container">
  <div class="site-nav-toggle">
    <div class="toggle" aria-label="切换导航栏">
      <span class="toggle-line toggle-line-first"></span>
      <span class="toggle-line toggle-line-middle"></span>
      <span class="toggle-line toggle-line-last"></span>
    </div>
  </div>

  <div class="site-meta">

    <a href="/" class="brand" rel="start">
      <span class="logo-line-before"><i></i></span>
      <h1 class="site-title">Benson's blog</h1>
      <span class="logo-line-after"><i></i></span>
    </a>
      <p class="site-subtitle" itemprop="description">最好的安排</p>
  </div>

  <div class="site-nav-right">
    <div class="toggle popup-trigger">
    </div>
  </div>
</div>




<nav class="site-nav">
  <ul id="menu" class="main-menu menu">
        <li class="menu-item menu-item-home">

    <a href="/" rel="section"><i class="fa fa-home fa-fw"></i>首页</a>

  </li>
        <li class="menu-item menu-item-tags">

    <a href="/tags/" rel="section"><i class="fa fa-tags fa-fw"></i>标签</a>

  </li>
        <li class="menu-item menu-item-categories">

    <a href="/categories/" rel="section"><i class="fa fa-th fa-fw"></i>分类</a>

  </li>
        <li class="menu-item menu-item-archives">

    <a href="/archives/" rel="section"><i class="fa fa-archive fa-fw"></i>归档</a>

  </li>
  </ul>
</nav>




</div>
    </header>

    
  <div class="back-to-top">
    <i class="fa fa-arrow-up"></i>
    <span>0%</span>
  </div>

  <a href="https://github.com/ZhouBencheng" class="github-corner" title="Follow me on GitHub" aria-label="Follow me on GitHub" rel="noopener" target="_blank"><svg width="80" height="80" viewBox="0 0 250 250" aria-hidden="true"><path d="M0,0 L115,115 L130,115 L142,142 L250,250 L250,0 Z"></path><path d="M128.3,109.0 C113.8,99.7 119.0,89.6 119.0,89.6 C122.0,82.7 120.5,78.6 120.5,78.6 C119.2,72.0 123.4,76.3 123.4,76.3 C127.3,80.9 125.5,87.3 125.5,87.3 C122.9,97.6 130.6,101.9 134.4,103.2" fill="currentColor" style="transform-origin: 130px 106px;" class="octo-arm"></path><path d="M115.0,115.0 C114.9,115.1 118.7,116.5 119.8,115.4 L133.7,101.6 C136.9,99.2 139.9,98.4 142.2,98.6 C133.8,88.0 127.5,74.4 143.8,58.0 C148.5,53.4 154.0,51.2 159.7,51.0 C160.3,49.4 163.2,43.6 171.4,40.1 C171.4,40.1 176.1,42.5 178.8,56.2 C183.1,58.6 187.2,61.8 190.9,65.4 C194.5,69.0 197.7,73.2 200.1,77.6 C213.8,80.2 216.3,84.9 216.3,84.9 C212.7,93.1 206.9,96.0 205.4,96.6 C205.1,102.4 203.0,107.8 198.3,112.5 C181.9,128.9 168.3,122.5 157.7,114.1 C157.9,116.9 156.7,120.9 152.7,124.9 L141.0,136.5 C139.8,137.7 141.6,141.9 141.8,141.8 Z" fill="currentColor" class="octo-body"></path></svg></a>


    <main class="main">
      <div class="main-inner">
        <div class="content-wrap">
          

          <div class="content post posts-expand">
            

    
  
  
  <article itemscope itemtype="http://schema.org/Article" class="post-block" lang="zh-CN">
    <link itemprop="mainEntityOfPage" href="http://www.zhoubencheng.com/2024/07/03/SQL/">

    <span hidden itemprop="author" itemscope itemtype="http://schema.org/Person">
      <meta itemprop="image" content="/images/ME.ico">
      <meta itemprop="name" content="Benson">
      <meta itemprop="description" content="Share my thoughts with you">
    </span>

    <span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
      <meta itemprop="name" content="Benson's blog">
    </span>
      <header class="post-header">
        <h1 class="post-title" itemprop="name headline">
          SQL
        </h1>

        <div class="post-meta">
            <span class="post-meta-item">
              <span class="post-meta-item-icon">
                <i class="far fa-calendar"></i>
              </span>
              <span class="post-meta-item-text">发表于</span>

              <time title="创建时间：2024-07-03 03:28:58" itemprop="dateCreated datePublished" datetime="2024-07-03T03:28:58+08:00">2024-07-03</time>
            </span>
              <span class="post-meta-item">
                <span class="post-meta-item-icon">
                  <i class="far fa-calendar-check"></i>
                </span>
                <span class="post-meta-item-text">更新于</span>
                <time title="修改时间：2024-10-08 20:59:39" itemprop="dateModified" datetime="2024-10-08T20:59:39+08:00">2024-10-08</time>
              </span>
            <span class="post-meta-item">
              <span class="post-meta-item-icon">
                <i class="far fa-folder"></i>
              </span>
              <span class="post-meta-item-text">分类于</span>
                <span itemprop="about" itemscope itemtype="http://schema.org/Thing">
                  <a href="/categories/DB/" itemprop="url" rel="index"><span itemprop="name">DB</span></a>
                </span>
            </span>

          
            <span class="post-meta-item" title="阅读次数" id="busuanzi_container_page_pv" style="display: none;">
              <span class="post-meta-item-icon">
                <i class="fa fa-eye"></i>
              </span>
              <span class="post-meta-item-text">阅读次数：</span>
              <span id="busuanzi_value_page_pv"></span>
            </span>

        </div>
      </header>

    
    
    
    <div class="post-body" itemprop="articleBody">

      
        <h2 id="sql语句">SQL语句</h2>
<p><strong>参考教程</strong>：<a target="_blank" rel="noopener" href="https://www.liaoxuefeng.com/wiki/1177760294764384">廖雪峰老师的SQL教程</a></p>
<h3 id="数据类型">数据类型</h3>
<table>
<colgroup>
<col style="width: 17%">
<col style="width: 19%">
<col style="width: 63%">
</colgroup>
<thead>
<tr>
<th>名称</th>
<th>类型</th>
<th>备注</th>
</tr>
</thead>
<tbody>
<tr>
<td>INT</td>
<td>整型</td>
<td>4bit</td>
</tr>
<tr>
<td>BIGINT</td>
<td>长整型</td>
<td>8bit</td>
</tr>
<tr>
<td>REAL</td>
<td>单精度浮点型</td>
<td>4bit</td>
</tr>
<tr>
<td>DOUBLE</td>
<td>双精度浮点型</td>
<td>8bit</td>
</tr>
<tr>
<td>DECIMAL(M, N)</td>
<td>高精度小数</td>
<td>M表示总共位数，N表示小数点位数</td>
</tr>
<tr>
<td>CHAR(N)</td>
<td>定长字符串</td>
<td>N个字符的字符串，插入不满足N个的字符串用空格填充</td>
</tr>
<tr>
<td>VARCHAR(N)</td>
<td>变长字符串</td>
<td>0~N个字符的字符串</td>
</tr>
<tr>
<td>BOOLEAN</td>
<td>布尔类型</td>
<td>True or False</td>
</tr>
<tr>
<td>DATE</td>
<td>日期类型</td>
<td></td>
</tr>
<tr>
<td>TIME</td>
<td>时间类型</td>
<td></td>
</tr>
<tr>
<td>DATETIME</td>
<td>日期 + 时间类型</td>
<td></td>
</tr>
</tbody>
</table>
<p>可以使用<code>NOT NULL</code>修饰一个字段类型，表示该字段<strong>不能为空</strong>
<span id="more"></span> ### 关系型数据库模型</p>
<p>关系型数据库在于使用行和列组成的表来存储数据，然通过<strong>表间关系</strong>和<strong>结构化查询语句(SQL)</strong>来进行数据存储、管理和检索，从而实现数据库的高效组织和操作</p>
<h4 id="主键">主键</h4>
<ul>
<li>主键：能通过某个字段唯一区分出不同的记录，这个字段称为主键</li>
<li>联合主键：关系型数据库允许通过多个字段唯一标识记录，将两个或者更多的字段都设置为主键。对于联合主键，允许其中一列重复，主要不是所有主键列都重复即可</li>
</ul>
<h4 id="外键">外键</h4>
<ul>
<li>一对多</li>
<li>多对多：通过一个中间表，关联两个一对多关系，形成多对多关系</li>
<li>一对一：大型数据库可以将一个表拆分成多个子表，分离出不常使用的数据，以提高表查询效率</li>
</ul>
<figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> thisTable;</span><br><span class="line"><span class="keyword">ADD</span> <span class="keyword">CONSTRAINT</span> foreignKey;  <span class="operator">/</span><span class="operator">/</span>指定约束名称</span><br><span class="line"><span class="keyword">FOREIGN</span> KEY (anotherCol);   <span class="operator">/</span><span class="operator">/</span>根据列名称指定本表中外键列</span><br><span class="line"><span class="keyword">REFERENCES</span> anotherTableName (colName); <span class="operator">/</span><span class="operator">/</span>指定另外一个表中的某一列作为外键约束参考</span><br></pre></td></tr></tbody></table></figure>
<h4 id="索引">索引</h4>
<p>当需要频繁根据某一列的数据进行查询时，可以为该列创建<strong>索引</strong>，索引的效率取决于索引列的值是否散列，列值越不相同，索引查询效率越高</p>
<figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> thiSTable;</span><br><span class="line"><span class="keyword">ADD</span> INDEX indexName (colName); <span class="operator">/</span><span class="operator">/</span>为colName列创建索引indexName</span><br></pre></td></tr></tbody></table></figure>
<h3 id="查询语句">查询语句</h3>
<ul>
<li><p>基本查询：查询表中的所有行，可以查询到一张表中的所有记录</p>
<figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span> <span class="keyword">FROM</span> tableName;</span><br></pre></td></tr></tbody></table></figure></li>
<li><p>条件查询：查询符合条件的记录</p>
<figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span> <span class="keyword">FROM</span> tableName <span class="keyword">WHERE</span> <span class="operator">&lt;</span>cond expr<span class="operator">&gt;</span>;</span><br><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span> <span class="keyword">FROM</span> tableName <span class="keyword">WHERE</span> <span class="operator">&lt;</span>cond expr1<span class="operator">&gt;</span> <span class="keyword">AND</span> <span class="operator">&lt;</span>cond expr2<span class="operator">&gt;</span>; <span class="operator">/</span><span class="operator">/</span>多条件查询</span><br></pre></td></tr></tbody></table></figure></li>
<li><p>投影查询：查询特定的列（别名和映射）</p>
<figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> col1, col2, col3 <span class="keyword">FROM</span> tableName;						<span class="operator">/</span><span class="operator">/</span>仅查询特定列</span><br><span class="line"><span class="keyword">SELECT</span> col1 ailas1, col2 alias2, col3 alias3 <span class="keyword">FROM</span> tableName; <span class="operator">/</span><span class="operator">/</span>结果集和原表的字段名可以不同</span><br></pre></td></tr></tbody></table></figure></li>
<li><p>排序：将查询结果按特定列的特定顺序输出，<code>ORDER BY</code>关键字默认按照升序输出，<code>DESC</code>关键字表示降序，<code>ASC</code>关键字表示升序但可以省略</p>
<figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> col1, col2, col3 <span class="keyword">FROM</span> tableName <span class="keyword">ORDER</span> <span class="keyword">BY</span> col1; 		<span class="operator">/</span><span class="operator">/</span>按照col1的列数据升序输出结果集</span><br><span class="line"><span class="keyword">SELECT</span> col1, col2, col3 <span class="keyword">FROM</span> tableName <span class="keyword">ORDER</span> <span class="keyword">BY</span> col1 <span class="keyword">DESC</span>; 	<span class="operator">/</span><span class="operator">/</span>降序输出结果集</span><br></pre></td></tr></tbody></table></figure></li>
<li><p>多表查询：多表查询又称为笛卡尔查询，结果集的列数是所有表的列数之<strong>和</strong>，结果集的行数是所有表的行数之<strong>积</strong></p>
<figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span> <span class="keyword">FROM</span> table1, table2</span><br></pre></td></tr></tbody></table></figure>
<p>当<code>table1</code>、<code>table2</code>中存在重复列名时可以使用投影查询中的列名<strong>别名</strong>方法</p>
<figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span></span><br><span class="line">	table1.id sid,</span><br><span class="line">	table1.name sname,</span><br><span class="line">	table2.id cid,</span><br><span class="line">	table2.name cname,</span><br><span class="line"><span class="keyword">FROM</span> table1, table2;</span><br></pre></td></tr></tbody></table></figure>
<p>在上述使用列名别名的方法有一些麻烦，可以使用<strong>表名别名</strong>的方法再次简化SQL语句</p>
<figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> </span><br><span class="line">	t1.id sid,</span><br><span class="line">	t1.name sname,</span><br><span class="line">	t2.id cid,</span><br><span class="line">	t2.name cname,</span><br><span class="line"><span class="keyword">FROM</span></span><br><span class="line">	table1 t1, table2 t2;</span><br></pre></td></tr></tbody></table></figure>
<p>用M行表和N行表进行多表查询会得到M *
N行记录，造成结果集非常巨大，需要谨慎使用</p></li>
<li><p>分页查询：对于查询结果比较大的情况，将结果集拆分成多页进行显示</p>
<figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> id, name, gender </span><br><span class="line"><span class="keyword">FROM</span> table1 </span><br><span class="line"><span class="keyword">ORDER</span> <span class="keyword">BY</span> id <span class="keyword">DESC</span></span><br><span class="line">LIMIT M <span class="keyword">OFFSET</span> N</span><br></pre></td></tr></tbody></table></figure>
<p><code>LIMIT M OFFSET N</code>设置一页中包含<code>M</code>条记录，显示第<code>N</code>条记录所在页</p></li>
<li><p>聚合查询：用于查询结果集进行数学统计，SQL提供一些专门统计函数</p>
<figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> <span class="built_in">COUNT</span>(<span class="operator">*</span>) <span class="operator">&lt;</span>OUTName<span class="operator">&gt;</span> <span class="keyword">FROM</span> table1</span><br></pre></td></tr></tbody></table></figure>
<p>使用<code>COUNT()</code>函数统计<code>table1</code>中有几行，注意输出结果时一行一列的<code>COUNT(*)</code>结果集，其中指定输出结果集即二维列表名称<code>&lt;OUTName&gt;</code>，以下是其他常用的聚合函数</p>
<table>
<thead>
<tr>
<th>函数</th>
<th>说明</th>
</tr>
</thead>
<tbody>
<tr>
<td>SUM()</td>
<td>计算某一列的和值，该列必须为数值类型</td>
</tr>
<tr>
<td>AVG()</td>
<td>计算某一列的平均值，该列必须为数值类型</td>
</tr>
<tr>
<td>MAX()</td>
<td>计算某一列的最大值</td>
</tr>
<tr>
<td>MIN()</td>
<td>计算某一列的最小值</td>
</tr>
</tbody>
</table></li>
<li><p>分组查询：当可以将表中的所有记录按某种条件进行分组时，比如<code>班级</code>、<code>性别</code>等，每次修改<code>WHERE</code>条件来显示单一次查询的结果比较麻烦，因此可以用<code>GROUP BY</code>关键字对记录进行分组</p>
<figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="operator">/</span><span class="operator">/</span>该<span class="keyword">SQL</span>语句将相同class_id的记录分为一组，计算各组记录的score均值</span><br><span class="line"><span class="keyword">SELECT</span> class_id, <span class="built_in">AVG</span>(score)</span><br><span class="line"><span class="keyword">FROM</span> table1</span><br><span class="line"><span class="keyword">GROUP</span> <span class="keyword">BY</span> class_id</span><br></pre></td></tr></tbody></table></figure>
<p>注意在分组查询中，查询结果中只能插入分组字段，分组无关字段不能插入</p></li>
<li><p>连接查询：一种特殊的多表查询，SQL语句中<code>FROM</code>指定一张主表后，将多张表的记录按照特定规则连接到主表上，而不是简单的<code>乘积</code>形式拼接</p>
<figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span> <span class="keyword">FROM</span> students s</span><br><span class="line"><span class="keyword">INNER</span> <span class="keyword">JOIN</span> class c</span><br><span class="line"><span class="keyword">ON</span> s.id <span class="operator">=</span> c.id</span><br></pre></td></tr></tbody></table></figure>
<p><code>INNER JOIN</code>关键字声明需要连接的表，<code>ON</code>声明连接条件，连接方式有如下四种</p>
<ul>
<li><code>INNER JOIN</code>：返回同时存在于两张表中的满足条件字段所对应的记录，交集</li>
<li><code>LEFT OUTER JOIN</code>：返回连接表的记录</li>
<li><code>RIGHT OUTER JOIN</code>：返回主表的记录</li>
<li><code>FULL OUTER JOIN</code>：返回主表和连接表的记录，并集</li>
</ul>
<p>注意对于<code>OUTER JOIN</code>连接的查询中，各表中不满足条件的记录使用<code>null</code>填充</p></li>
</ul>
<h3 id="修改语句">修改语句</h3>
<h4 id="插入">插入</h4>
<figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> tableName (col1, col2, col3) <span class="keyword">VALUES</span> (val1, val2, val3)</span><br></pre></td></tr></tbody></table></figure>
<h4 id="更新">更新</h4>
<figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">UPDATE</span> tableName <span class="keyword">SET</span> col1<span class="operator">=</span>val1, col2<span class="operator">=</span>val2, ... <span class="keyword">WHERE</span> col3<span class="operator">=</span>val3</span><br></pre></td></tr></tbody></table></figure>
<p>注意<code>UPDATE</code>尽量搭配<code>WHERE</code>使用，否则将改变整张表的记录</p>
<h4 id="删除">删除</h4>
<figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">DELETE</span> <span class="keyword">FROM</span> tableName <span class="keyword">WHERE</span> ...</span><br></pre></td></tr></tbody></table></figure>
<p>注意<code>DELETE</code>语句也尽量搭配<code>WHERE</code>使用，否则将删除整张表的记录</p>
<h3 id="其他关键字">其他关键字</h3>
<ul>
<li><p><code>IN</code>：用于简略多个<code>OR</code>运算的写法，判断某条记录是否在集合中</p>
<figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span> <span class="keyword">FROM</span> table1 <span class="keyword">WHERE</span> col1 <span class="keyword">IN</span> (val1, val2)</span><br></pre></td></tr></tbody></table></figure>
<p>同时也可以使用<code>IN</code>关键字进行<strong>子查询</strong>，即在<code>IN</code>中再使用<code>SQL</code>语句，对结果集进行查询</p>
<figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> emp_name</span><br><span class="line"><span class="keyword">FROM</span> employees</span><br><span class="line"><span class="keyword">WHERE</span> emp_department <span class="keyword">IN</span> (<span class="keyword">SELECT</span> dept_name <span class="keyword">FROM</span> departments <span class="keyword">WHERE</span> location <span class="operator">=</span> <span class="string">'New York'</span>);</span><br></pre></td></tr></tbody></table></figure></li>
<li><p><code>IS</code>：用于判断某个值是否为<code>NULL</code>，不能直接用<code>=</code>进行比较，因为<code>NULL</code>表示未知的值，不能直接使用普通等号进行比较</p>
<figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> column1, column2</span><br><span class="line"><span class="keyword">FROM</span> table_name</span><br><span class="line"><span class="keyword">WHERE</span> column1 <span class="keyword">IS</span> <span class="keyword">NULL</span>;</span><br></pre></td></tr></tbody></table></figure></li>
</ul>

    </div>

    
    
    

      <footer class="post-footer">
          
          <div class="post-tags">
              <a href="/tags/%E6%95%B0%E6%8D%AE%E5%BA%93/" rel="tag"><i class="fa fa-tag"></i> 数据库</a>
              <a href="/tags/SQL/" rel="tag"><i class="fa fa-tag"></i> SQL</a>
          </div>

        


        
    <div class="post-nav">
      <div class="post-nav-item">
    <a href="/2024/07/02/MyBatis/" rel="prev" title="MyBatis">
      <i class="fa fa-chevron-left"></i> MyBatis
    </a></div>
      <div class="post-nav-item">
    <a href="/2024/07/04/JSP/" rel="next" title="JSP">
      JSP <i class="fa fa-chevron-right"></i>
    </a></div>
    </div>
      </footer>
    
  </article>
  
  
  



          </div>
          

<script>
  window.addEventListener('tabs:register', () => {
    let { activeClass } = CONFIG.comments;
    if (CONFIG.comments.storage) {
      activeClass = localStorage.getItem('comments_active') || activeClass;
    }
    if (activeClass) {
      let activeTab = document.querySelector(`a[href="#comment-${activeClass}"]`);
      if (activeTab) {
        activeTab.click();
      }
    }
  });
  if (CONFIG.comments.storage) {
    window.addEventListener('tabs:click', event => {
      if (!event.target.matches('.tabs-comment .tab-content .tab-pane')) return;
      let commentClass = event.target.classList[1];
      localStorage.setItem('comments_active', commentClass);
    });
  }
</script>

        </div>
          
  
  <div class="toggle sidebar-toggle">
    <span class="toggle-line toggle-line-first"></span>
    <span class="toggle-line toggle-line-middle"></span>
    <span class="toggle-line toggle-line-last"></span>
  </div>

  <aside class="sidebar">
    <div class="sidebar-inner">

      <ul class="sidebar-nav motion-element">
        <li class="sidebar-nav-toc">
          文章目录
        </li>
        <li class="sidebar-nav-overview">
          站点概览
        </li>
      </ul>

      <!--noindex-->
      <div class="post-toc-wrap sidebar-panel">
          <div class="post-toc motion-element"><ol class="nav"><li class="nav-item nav-level-2"><a class="nav-link" href="#sql%E8%AF%AD%E5%8F%A5"><span class="nav-number">1.</span> <span class="nav-text">SQL语句</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#%E6%95%B0%E6%8D%AE%E7%B1%BB%E5%9E%8B"><span class="nav-number">1.1.</span> <span class="nav-text">数据类型</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#%E4%B8%BB%E9%94%AE"><span class="nav-number">1.1.1.</span> <span class="nav-text">主键</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#%E5%A4%96%E9%94%AE"><span class="nav-number">1.1.2.</span> <span class="nav-text">外键</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#%E7%B4%A2%E5%BC%95"><span class="nav-number">1.1.3.</span> <span class="nav-text">索引</span></a></li></ol></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E6%9F%A5%E8%AF%A2%E8%AF%AD%E5%8F%A5"><span class="nav-number">1.2.</span> <span class="nav-text">查询语句</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E4%BF%AE%E6%94%B9%E8%AF%AD%E5%8F%A5"><span class="nav-number">1.3.</span> <span class="nav-text">修改语句</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#%E6%8F%92%E5%85%A5"><span class="nav-number">1.3.1.</span> <span class="nav-text">插入</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#%E6%9B%B4%E6%96%B0"><span class="nav-number">1.3.2.</span> <span class="nav-text">更新</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#%E5%88%A0%E9%99%A4"><span class="nav-number">1.3.3.</span> <span class="nav-text">删除</span></a></li></ol></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E5%85%B6%E4%BB%96%E5%85%B3%E9%94%AE%E5%AD%97"><span class="nav-number">1.4.</span> <span class="nav-text">其他关键字</span></a></li></ol></li></ol></div>
      </div>
      <!--/noindex-->

      <div class="site-overview-wrap sidebar-panel">
        <div class="site-author motion-element" itemprop="author" itemscope itemtype="http://schema.org/Person">
    <img class="site-author-image" itemprop="image" alt="Benson"
      src="/images/ME.ico">
  <p class="site-author-name" itemprop="name">Benson</p>
  <div class="site-description" itemprop="description">Share my thoughts with you</div>
</div>
<div class="site-state-wrap motion-element">
  <nav class="site-state">
      <div class="site-state-item site-state-posts">
          <a href="/archives/">
        
          <span class="site-state-item-count">17</span>
          <span class="site-state-item-name">日志</span>
        </a>
      </div>
      <div class="site-state-item site-state-categories">
            <a href="/categories/">
          
        <span class="site-state-item-count">7</span>
        <span class="site-state-item-name">分类</span></a>
      </div>
      <div class="site-state-item site-state-tags">
            <a href="/tags/">
          
        <span class="site-state-item-count">25</span>
        <span class="site-state-item-name">标签</span></a>
      </div>
  </nav>
</div>
  <div class="links-of-author motion-element">
      <span class="links-of-author-item">
        <a href="https://github.com/ZhouBencheng" title="GitHub → https:&#x2F;&#x2F;github.com&#x2F;ZhouBencheng" rel="noopener" target="_blank"><i class="fab fa-github fa-fw"></i>GitHub</a>
      </span>
      <span class="links-of-author-item">
        <a href="mailto:zhoubencheng2022@163.com" title="E-Mail → mailto:zhoubencheng2022@163.com" rel="noopener" target="_blank"><i class="fa fa-envelope fa-fw"></i>E-Mail</a>
      </span>
  </div>



      </div>

    </div>
  </aside>
  <div id="sidebar-dimmer"></div>


      </div>
    </main>

    <footer class="footer">
      <div class="footer-inner">
        

        

<div class="copyright">
  
  &copy; 
  <span itemprop="copyrightYear">2024</span>
  <span class="with-love">
    <i class="fa fa-heart"></i>
  </span>
  <span class="author" itemprop="copyrightHolder">Benson</span>
</div>

<!--
  <div class="powered-by">由 <a href="https://hexo.io/" class="theme-link" rel="noopener" target="_blank">Hexo</a> & <a href="https://pisces.theme-next.org/" class="theme-link" rel="noopener" target="_blank">NexT.Pisces</a> 强力驱动
  </div>
-->

        
<div class="busuanzi-count">
  <script async src="https://busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script>
    <span class="post-meta-item" id="busuanzi_container_site_uv" style="display: none;">
      <span class="post-meta-item-icon">
        <i class="fa fa-user"></i>
      </span>
      <span class="site-uv" title="总访客量">
        <span id="busuanzi_value_site_uv"></span>
      </span>
    </span>
    <span class="post-meta-divider">|</span>
    <span class="post-meta-item" id="busuanzi_container_site_pv" style="display: none;">
      <span class="post-meta-item-icon">
        <i class="fa fa-eye"></i>
      </span>
      <span class="site-pv" title="总访问量">
        <span id="busuanzi_value_site_pv"></span>
      </span>
    </span>
</div>








      </div>
    </footer>
  </div>

  
  <script src="/lib/anime.min.js"></script>
  <script src="/lib/velocity/velocity.min.js"></script>
  <script src="/lib/velocity/velocity.ui.min.js"></script>

<script src="/js/utils.js"></script>

<script src="/js/motion.js"></script>


<script src="/js/schemes/pisces.js"></script>


<script src="/js/next-boot.js"></script>


  <script defer src="/lib/three/three.min.js"></script>
    <script defer src="/lib/three/canvas_lines.min.js"></script>


  













<script>
if (document.querySelectorAll('pre.mermaid').length) {
  NexT.utils.getScript('//cdn.jsdelivr.net/npm/mermaid@8/dist/mermaid.min.js', () => {
    mermaid.initialize({
      theme    : 'dark',
      logLevel : 3,
      flowchart: { curve     : 'linear' },
      gantt    : { axisFormat: '%m/%d/%Y' },
      sequence : { actorMargin: 50 }
    });
  }, window.mermaid);
}
</script>


  

  

  

</body>
</html>
